package swd.work.dataview_wizard;

import com.pub.database.*;
import java.util.*;
import com.pub.web.*;

public class Business {
   QueryBean gqbnDAO=null;
   public Business(QueryBean pqbnDAO){
      gqbnDAO=pqbnDAO;
   }

   String getDataViewSeqno(View view) throws Exception{
       String lstrSQL=null;
       String lstrSno=null;
       try{

           lstrSQL="select count(*) from wkf_dataview_master where prg_no='"+view.getprg_no();
           lstrSQL+="' and tables_list='"+view.gettables_list()+"' and replace(fields_list,'''','*')='";
           lstrSQL+=view.getfields_list().toString().replaceAll("'","*")+"'";
           if(view.getsql_condition()!=null){
               lstrSQL+=" and replace(nvl(sql_condition,'null'),'''','*')='";
               lstrSQL+=view.getsql_condition().replaceAll("'","*")+"'";
           }
           if(view.getorderby_fields()!=null){
               lstrSQL+=" and replace(nvl(orderby_fields,'null'),'''','*')='";
               lstrSQL+=view.getorderby_fields().toString().replaceAll("'","*")+"'";

           }
           if(view.gethaving_condition()!=null){
               lstrSQL+=" and replace(nvl(having_condition,'null'),'''','*')='";
               lstrSQL+=view.gethaving_condition().toString().replaceAll("'","*")+"'";
           }


           System.out.println("SQLbbb================>"+lstrSQL);

           lstrSno=gqbnDAO.readSingleValueData(lstrSQL);
           if(Integer.parseInt(lstrSno)>0){
           //表示在一樣的prg_no找到相同SQL語句
              return "error";
           }
           else{
               lstrSQL="select nvl(max(dataview_seqno),0)+1 as maxno from wkf_dataview_master where prg_no='"+view.getprg_no()+"'";
               lstrSno=gqbnDAO.readSingleValueData(lstrSQL);
               return lstrSno;
           }

       }
       catch(Exception e){
           throw new Exception(e.toString());
       }
   }
   String selectTableDataToFld(View view) throws Exception {
      String lstrSQL=null;
      Hashtable<String,Object> lhtTableData=null;
      try{
         lstrSQL="select dataview_seqno,prg_no,fields_list,show_filter,tables_list,sql_condition,groupby_fileds,orderby_fields,show_jump_page,show_rows_info,enable_order_by,having_condition,show_excel_button,show_rows_of_page,show_addnew_button,show_button_on_top,show_button_on_buttom,show_jump_page_button,show_header_when_nodata,db_alias from wkf_dataview_master where prg_no='" + view.getprg_no() + "'" + " and seqno='" + view.getseqno() + "'";
         lhtTableData=gqbnDAO.readSingleRowData(lstrSQL);
         if(lhtTableData==null){
            return "查無資料";
         }
         view.setdataview_seqno(String.valueOf((lhtTableData.get("DATAVIEW_SEQNO").toString())));
         view.setprg_no(String.valueOf((lhtTableData.get("PRG_NO").toString())));
         view.setfields_list(String.valueOf((lhtTableData.get("FIELDS_LIST").toString())));
         view.setshow_filter(String.valueOf((lhtTableData.get("SHOW_FILTER").toString())));
         view.settables_list(String.valueOf((lhtTableData.get("TABLES_LIST").toString())));
         view.setsql_condition(String.valueOf((lhtTableData.get("SQL_CONDITION").toString())));
         view.setgroupby_fields(String.valueOf((lhtTableData.get("GROUPBY_FIELDS").toString())));
         view.setorderby_fields(String.valueOf((lhtTableData.get("ORDERBY_FIELDS").toString())));
         view.setshow_jump_page(String.valueOf((lhtTableData.get("SHOW_JUMP_PAGE").toString())));
         view.setshow_rows_info(String.valueOf((lhtTableData.get("SHOW_ROWS_INFO").toString())));
         view.setenable_order_by(String.valueOf((lhtTableData.get("ENABLE_ORDER_BY").toString())));
         view.sethaving_condition(String.valueOf((lhtTableData.get("HAVING_CONDITION").toString())));
         view.setshow_excel_button(String.valueOf((lhtTableData.get("SHOW_EXCEL_BUTTON").toString())));
         view.setshow_rows_of_page(String.valueOf((lhtTableData.get("SHOW_ROWS_OF_PAGE").toString())));
         view.setshow_addnew_button(String.valueOf((lhtTableData.get("SHOW_ADDNEW_BUTTON").toString())));
         view.setshow_button_on_top(String.valueOf((lhtTableData.get("SHOW_BUTTON_ON_TOP").toString())));
         view.setshow_button_on_buttom(String.valueOf((lhtTableData.get("SHOW_BUTTON_ON_BUTTOM").toString())));
         view.setshow_jump_page_button(String.valueOf((lhtTableData.get("SHOW_JUMP_PAGE_BUTTON").toString())));
         view.setshow_header_when_nodata(String.valueOf((lhtTableData.get("SHOW_HEADER_WHEN_NODATA").toString())));
         view.setorderby_last_field_seq(String.valueOf((lhtTableData.get("ORDERBY_LAST_FIELD_SEQ").toString())));
         view.setdb_alias(String.valueOf((lhtTableData.get("DB_ALIAS").toString())));

         return "";
      }
      catch(Exception e){
         throw new Exception(e.toString());
      }
      finally{
         lhtTableData.clear();
         lhtTableData=null;
      }
   }
   String exeUpdSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{

         lvtrParam.add(view.getdataview_seqno()==null ? null :String.valueOf(view.getdataview_seqno()));
         lvtrParam.add(view.getprg_no()==null ? null :String.valueOf(view.getprg_no()));
         lvtrParam.add(view.getfields_list()==null ? null :String.valueOf(view.getfields_list()));
         lvtrParam.add(view.getshow_filter()==null ? null :String.valueOf(view.getshow_filter()));
         lvtrParam.add(view.gettables_list()==null ? null :String.valueOf(view.gettables_list()));
         lvtrParam.add(view.getsql_condition()==null ? null :String.valueOf(view.getsql_condition()));
         lvtrParam.add(view.getgroupby_fields()==null ? null :String.valueOf(view.getgroupby_fields()));
         lvtrParam.add(view.getorderby_fields()==null ? null :String.valueOf(view.getorderby_fields()));
         lvtrParam.add(view.getshow_jump_page()==null ? null :String.valueOf(view.getshow_jump_page()));
         lvtrParam.add(view.getshow_rows_info()==null ? null :String.valueOf(view.getshow_rows_info()));
         lvtrParam.add(view.getenable_order_by()==null ? null :String.valueOf(view.getenable_order_by()));
         lvtrParam.add(view.gethaving_condition()==null ? null :String.valueOf(view.gethaving_condition()));
         lvtrParam.add(view.getshow_excel_button()==null ? null :String.valueOf(view.getshow_excel_button()));
         lvtrParam.add(view.getshow_rows_of_page()==null ? null :String.valueOf(view.getshow_rows_of_page()));
         lvtrParam.add(view.getshow_addnew_button()==null ? null :String.valueOf(view.getshow_addnew_button()));
         lvtrParam.add(view.getshow_button_on_top()==null ? null :String.valueOf(view.getshow_button_on_top()));
         lvtrParam.add(view.getshow_button_on_buttom()==null ? null :String.valueOf(view.getshow_button_on_buttom()));
         lvtrParam.add(view.getshow_jump_page_button()==null ? null :String.valueOf(view.getshow_jump_page_button()));
         lvtrParam.add(view.getorderby_last_field_seq()==null ? null :String.valueOf(view.getorderby_last_field_seq()));
         lvtrParam.add(view.getshow_header_when_nodata()==null ? null :String.valueOf(view.getshow_header_when_nodata()));
         lvtrParam.add(view.getdb_alias()==null ? null :String.valueOf(view.getdb_alias()));
         lstrSQL="update wkf_dataview_master set seqno=?,prg_no=?,show_filter=?,tables_list=?,sql_condition=?,groupby_fields=?,orderby_fields=?,show_jump_page=?,show_rows_info=?,enable_order_by=?,having_condition=?,show_excel_button=?,show_rows_of_page=?,show_addnew_button=?,show_button_on_top=?,show_button_on_buttom=?,show_jump_page_button=?,orderby_last_field_seq=?,show_header_when_nodata=?,db_alias=? where prg_no='" + view.getprg_no() + "'" + " and seqno='" + view.getseqno() + "'";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            return "wkf_dataview_master";
         }
         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e.toString());
      }
      finally{
         lvtrParam.clear();
      }
   }
   String exeInsSQL(View view) throws Exception{
      String lstrSQL=new String();
      String lstrMsg=null;
      Vector<Object> lvtrParam = new Vector<Object>();
      try{

          view.setdataview_seqno(getDataViewSeqno(view));
          if(view.getdataview_seqno()!=null && !view.getdataview_seqno().equals("error")){
          lvtrParam.add((view.getprg_no()==null ? null : view.getprg_no()));
          lvtrParam.add(view.getdataview_seqno());
          lvtrParam.add((view.getfields_list()==null ? null : view.getfields_list()));
          lvtrParam.add((view.gettables_list()==null ? null : view.gettables_list()));
          lvtrParam.add((view.getsql_condition()==null ? null : view.getsql_condition()));
          lvtrParam.add((view.getorderby_fields()==null ? null : view.getorderby_fields()));
          lvtrParam.add((view.getgroupby_fields()==null ? null : view.getgroupby_fields()));
          lvtrParam.add((view.gethaving_condition()==null ? null : view.gethaving_condition()));
          lvtrParam.add((view.getshow_jump_page_button()==null ? null : view.getshow_jump_page_button()));
          lvtrParam.add((view.getshow_button_on_top()==null ? null : view.getshow_button_on_top()));
          lvtrParam.add((view.getshow_button_on_buttom()==null ? null : view.getshow_button_on_buttom()));
          lvtrParam.add((view.getshow_filter()==null ? null : view.getshow_filter()));
          lvtrParam.add((view.getshow_jump_page()==null ? null : view.getshow_jump_page()));
          lvtrParam.add((view.getshow_rows_info()==null ? null : view.getshow_rows_info()));
          lvtrParam.add((view.getshow_rows_of_page()==null ? null : view.getshow_rows_of_page()));
          lvtrParam.add((view.getshow_excel_button()==null ? null : view.getshow_excel_button()));
          lvtrParam.add((view.getenable_order_by()==null ? null : view.getenable_order_by()));
          lvtrParam.add((view.getshow_header_when_nodata()==null ? null : view.getshow_header_when_nodata()));
          lvtrParam.add((view.getshow_addnew_button()==null ? null : view.getshow_addnew_button()));
          lvtrParam.add((view.getorderby_last_field_seq()==null ? null : view.getorderby_last_field_seq()));
          lvtrParam.add((view.getdb_alias()==null ? null : view.getdb_alias()));
          lstrSQL="insert into wkf_dataview_master(prg_no,dataview_seqno,fields_list,tables_list,sql_condition,orderby_fields,groupby_fields,having_condition,show_jump_page_button,show_button_on_top,show_button_on_buttom,show_filter,show_jump_page,show_rows_info,show_rows_of_page,show_excel_button,enable_order_by,show_header_when_nodata,show_addnew_button,orderby_last_field_seqno,db_alias) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            lstrMsg = "wkf_dataview_master";
         }

         lvtrParam.clear();
          }
          else{ lstrMsg = "在"+view.getprg_no()+"中，相同SQL語句的DataView已存在！";}
          return lstrMsg;

      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }
   String checkNotNullFld(View view) throws Exception{
      StringBuffer lstbNullFldList=new StringBuffer();
      lstbNullFldList.append(view.getprg_no()==null ? view.getprg_noTitle() + "," : "");
      lstbNullFldList.append(view.getfields_list()==null ? view.getfields_listTitle() + "," : "");
      lstbNullFldList.append(view.gettables_list()==null ? view.gettables_listTitle() + "," : "");
      return (lstbNullFldList.toString().equals("") ? "" : lstbNullFldList.delete(lstbNullFldList.length()-1,lstbNullFldList.length()).toString());
   }
   void setInitValueForAdd(View view,String pstrUserID) throws Exception{
   }
   void setReadonlyForAdd(View view) throws Exception{
   }
   boolean checkPrgnoExists(View view) throws Exception{
       String lstrSQL=null;
       String iCount=null;
       try{
           //check prg_no是否存在wkf_sys_prg
           lstrSQL="select count(*) from wkf_sys_prg where prg_no='"+view.getprg_no()+"'";
           iCount=gqbnDAO.readSingleValueData(lstrSQL);
           if(Integer.parseInt(iCount)>0){return true;}
           else{return false;}

       }
       catch(Exception e){
           throw new Exception(e);
       }
   }
   void insertDefaultProperty(View view) throws Exception{
       StringBuffer lstbSQL=new StringBuffer();

       lstbSQL.append("insert into wkf_dataview_detail select '");
       lstbSQL.append(view.getprg_no());
       lstbSQL.append("','");
       lstbSQL.append(view.getdataview_seqno());
       lstbSQL.append("',att_kind,att_name,att_default_value,att_type from wkf_dataview_attribute");

       gqbnDAO.executeSQL(lstbSQL.toString());
   }

   void insertFieldsProperty(View view) throws Exception{
       ArrayList<String> laryData=null;
       ArrayList<String> larySQL=new ArrayList<String>();

       try{
           gqbnDAO.querySQL(combindSQL(view));
           laryData = gqbnDAO.getArlcolumnLabel(); //主要是要取得欄位的list

           for (int i = 0; i < laryData.size(); i++) {
               larySQL.add("insert into wkf_dataview_detail values ('" +
                           view.getprg_no() + "','" + view.getdataview_seqno() +
                           "','FLD','dataView.STR_FIELD_NAME','" +
                           laryData.get(i).toString() + "','String')");
               larySQL.add("insert into wkf_dataview_detail values ('" +
                           view.getprg_no() + "','" + view.getdataview_seqno() +
                           "','FLD','dataView.STR_FIELD_VALUE','" +
                           laryData.get(i).toString() + "','String')");
           }

           gqbnDAO.executeSQL(larySQL);
       }
       catch(Exception e){
           throw new Exception(e);
       }
       finally{
           if(larySQL!=null){
               larySQL.clear();
               larySQL=null;
           }
           if(laryData!=null){
               laryData.clear();
               laryData=null;
           }
       }
   }
   String combindSQL(View view) throws Exception{
       StringBuffer lstbSQL=new StringBuffer();

       lstbSQL.append("select ");
       lstbSQL.append(view.getfields_list());
       lstbSQL.append(" from ");
       lstbSQL.append(view.gettables_list());
       lstbSQL.append(view.getsql_condition() != null &&
                     !
                     view.getsql_condition().trim().equals(
                             "") ?
                     " where " + view.getsql_condition() + " and 1=2": //這兒要特別注意，使用1=2是要取得欄位名稱及順序
                     "");
       lstbSQL.append(view.getgroupby_fields() != null &&
                     !view.getgroupby_fields().trim().equals("") ?
                     " group by " + view.getgroupby_fields() : "");
       lstbSQL.append(view.gethaving_condition() != null &&
                     !view.gethaving_condition().trim().equals("") ?
                     " having " + view.gethaving_condition() : "");

       lstbSQL.append(view.getorderby_fields() != null &&
                     !view.getorderby_fields().trim().
                     equals("") ?
                     " order by " + view.getorderby_fields() +
                     " " : "");
       lstbSQL.append(view.getorderby_last_field_seq() != null &&
                     !
                     view.getorderby_last_field_seq().trim().equals("") ?
                      view.getorderby_last_field_seq() : "");

       return lstbSQL.toString();
   }
   void setdb_alias_options(View view) throws Exception {
       StringBuffer lstbSQL = null;
       try {
           lstbSQL = new StringBuffer();
           lstbSQL.append(
                   "select db_no,db_no from wkf_db_file order by 1");

           view.setdb_alias_options(WebFunction.transferRSToHtmlOption(view.
                   getDBAlias(), view.getDBDebug(), view.getSourceCharset(),
                   view.getTargetCharset(), lstbSQL.toString(), view.getdb_alias()));
       } catch (Exception e) {
           e.printStackTrace();
           throw new Exception(e.toString());
       } finally {
           lstbSQL = null;
       }
    }
}
